package lab1.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import lab1.db.DatabaseManager;
import lab1.vo.RoleVO;
import lab1.vo.UserVO;
import lab1.vo.ValueObject;

/**
 * 
 * @author Arty
 * 
 */

public class RolesDAO implements DAO {

	public void create(ValueObject vo) {
		DatabaseManager db = new DatabaseManager();
		RoleVO rol = (RoleVO) vo;

		StringBuilder sb = new StringBuilder();
		PreparedStatement ps = null;
		Connection con = null;
		try {
			con = db.getConnection();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		sb.append(" INSERT INTO user (role_desc, role_name)");
		sb.append(" VALUES(?,?)");

		try {
			ps = con.prepareStatement(sb.toString());
			ps.setString(1, rol.getDescription());
			ps.setString(2, rol.getName());
			ps.executeUpdate();

		} catch (SQLException e) {
			System.out.println("SQL Error: " + e.getMessage());
		}

		try {
			con.close();
		} catch (SQLException e) {
			System.out.println("Connection close error: " + e.getMessage());
		}
	}// End create(ValueObject vo)

	public boolean delete(long i) {
		DatabaseManager db = new DatabaseManager();
		Connection con = null;
		StringBuilder sb = new StringBuilder();
		PreparedStatement ps = null;

		try {
			con = db.getConnection();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		sb.append(" DELETE FROM roles");
		sb.append(" WHERE role_id=?");

		try {
			ps = con.prepareStatement(sb.toString());
			ps.setInt(1, (int) i);
			ps.executeUpdate();

			return true;
		} catch (SQLException e) {
			System.out.println("SQL Error: " + e.getMessage());
		}
		return false;
	}// End delete(long i)

	public boolean update(ValueObject vo) {
		if (vo == null)
			return false;
		DatabaseManager db = new DatabaseManager();
		RoleVO rol = (RoleVO) vo;
		StringBuilder sb = new StringBuilder();
		PreparedStatement ps = null;
		Connection con = null;
		try {
			con = db.getConnection();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		sb.append(" UPDATE roles SET");
		sb.append(" role_name=?");
		sb.append(" ,role_desc=?");
		sb.append(" WHERE role_id=?");

		try {
			ps = con.prepareStatement(sb.toString());
			ps.setString(1, rol.getName());
			ps.setString(2, rol.getDescription());
			ps.setInt(3, rol.getId());
			ps.executeUpdate();

			return true;
		} catch (SQLException e) {
			System.out.println(e.getLocalizedMessage());
			return false;
		}
	}// End update(ValueObject vo)

	public ArrayList<ValueObject> retrieve() {
		ArrayList<ValueObject> arr = new ArrayList<ValueObject>();
		DatabaseManager db = new DatabaseManager();
		StringBuilder sb = new StringBuilder();
		PreparedStatement ps = null;
		Connection con = null;
		try {
			con = db.getConnection();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		sb.append(" SELECT * FROM roles");

		try {
			ps = con.prepareStatement(sb.toString());
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				RoleVO rol = new RoleVO();
				rol.setId(Integer.parseInt(rs.getString("role_id"), 10));
				rol.setName(rs.getString("role_name"));
				rol.setDescription(rs.getString("role_desc"));
				arr.add(rol);
				return arr;
			}
		} catch (SQLException e) {
			System.out.println("SQL Error: " + e.getMessage());
		}

		try {
			con.close();
		} catch (SQLException e) {
			System.out.println("Connection close error: " + e.getMessage());
		}

		return null;

	}// End - retrieve()

	public ValueObject retrieve(long i) {
		DatabaseManager db = new DatabaseManager();
		RoleVO rol = null;
		StringBuilder sb = new StringBuilder();
		PreparedStatement ps = null;
		Connection con = null;
		try {
			con = db.getConnection();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		sb.append(" SELECT * FROM roles WHERE");
		sb.append("role_id=?");

		try {
			ps = con.prepareStatement(sb.toString());
			ps.setInt(1, (int) i);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				rol.setId(Integer.parseInt(rs.getString("role_id"), 10));
				rol.setName(rs.getString("role_name"));
				rol.setDescription(rs.getString("role_desc"));
				return rol;
			}
		} catch (SQLException e) {
			System.out.println("SQL Error: " + e.getMessage());
		}

		try {
			con.close();
		} catch (SQLException e) {
			System.out.println("Connection close error: " + e.getMessage());
		}

		return null;
	}// End - retrieve(long i)

	public ArrayList<ValueObject> retrieve(ValueObject vo) {
		if (vo == null)
			return null;
		ArrayList<ValueObject> arr = new ArrayList<ValueObject>();
		DatabaseManager db = new DatabaseManager();
		UserVO us = (UserVO) vo;
		StringBuilder sb = new StringBuilder();
		PreparedStatement ps = null;
		Connection con = null;
		try {
			con = db.getConnection();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		sb.append(" SELECT * FROM `user` u");
		sb.append(" INNER JOIN `groups` gr");
		sb.append(" ON u.user_gr_id = gr.group_id");
		sb.append(" INNER JOIN `rel_groups_roles` rel");
		sb.append(" ON rel.rel_gr_id = gr.group_id");
		sb.append(" INNER JOIN `roles` rol");
		sb.append(" ON rel.rel_rol_id = rol.role_id");
		sb.append(" WHERE gr.group_id = ?");
		sb.append(" GROUP BY rol.role_id");

		try {
			ps = con.prepareStatement(sb.toString());
			ps.setInt(1, (int) us.getGroupId());
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				RoleVO newRole = new RoleVO();
				newRole.setId(Integer.parseInt(rs.getString("role_id"), 10));
				newRole.setName(rs.getString("role_name"));
				newRole.setDescription(rs.getString("role_desc"));
				arr.add(newRole);
			}
		} catch (SQLException e) {
			System.out.println("SQL Error: " + e.getMessage());
		}

		try {
			con.close();
		} catch (SQLException e) {
			System.out.println("Connection close error: " + e.getMessage());
		}

		return arr;
	}// End - retrieve(UserVO us)
}
